
package com.be.beadmin.product.repository;

import com.be.beadmin.product.entity.ProductManufactInfor;
import com.be.beadmin.project.entity.Info;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

/**
* @author zjm
* @date 2023-03-19
**/
public interface ProductManufactInforRepository extends JpaRepository<ProductManufactInfor, String>, JpaSpecificationExecutor<ProductManufactInfor> {

    ProductManufactInfor findProductManufactInforByIdAndDelete(String id, Integer delete);


    @Query(value = "select i from Info i where i.id in (select b.infoId from BatchProduct b where b.id in (select p.batchProductId from ProductManufactInfor p where p.id = ?1 and p.delete = 0) and b.delete = 0)")
    Info findInfoByProductManufactInforAndDelete(String id, Integer delete);
    @Query(value = "select pmi.is_schduling from t_om_product_manufact_infor pmi where pmi.id = ?1 and is_delete = ?2", nativeQuery = true)
    int  findIsSchduling(String id, Integer delete);


    @Query(value = "select pmi.sequence from t_om_product_manufact_infor pmi where pmi.id = ?1 and is_delete = ?2", nativeQuery = true)
    int findSequenceByIdAndDelete(String id, Integer delete);


    List<ProductManufactInfor> findProductManufactInforByIdInAndDeleteOrderBySequence(List<String> id, Integer delete);

    ProductManufactInfor findProductManufactInforById(String id);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductCodeAndDelete(String batchProductCode, Integer delete);

    @Query(value = "select pmi.id from t_om_product_manufact_infor pmi where pmi.batch_product_code = ?1 and is_delete = ?2", nativeQuery = true)
    List<String> findIdByBatchProductCodeAndDelete(String batchProductCode, Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductIdAndDelete(String batchProductId, Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductIdInAndDelete(List<String> batchProductId, Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductIdInAndSequenceAndDelete(List<String> batchProductId,Integer sequence,Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductIdAndPlanManufactEndDateAfterAndDelete(String batchProductId, Date date ,Integer delete);

    @Query(value = "select sum(pmi.stand_manufact_workhour) from t_om_product_manufact_infor pmi where pmi.batch_product_id = ?1 and is_delete = ?2", nativeQuery = true)
    Float sumWorkHourByBatchProductIdAndDelete(String batchProductId, Integer delete);


    @Query(value = "select max(pmi.sequence) from t_om_product_manufact_infor pmi where pmi.batch_product_id = ?1 and is_delete = ?2", nativeQuery = true)
    int findProcessLastSeqAndDelete(String batchProductId, Integer delete);

    @Query(value = "select min(pmi.sequence) from t_om_product_manufact_infor pmi where pmi.batch_product_id = ?1 and is_delete = ?2", nativeQuery = true)
    int findProcessFirstSeqAndDelete(String batchProductId, Integer delete);
    /**
     * select * from products where unitprice=(select max(unitprice)from products);
     * @param batchProductId
     * @param delete
     * @return
     */
    @Query(value = "select * from t_om_product_manufact_infor pmi where pmi.sequence = (select max(pmi.sequence) from t_om_product_manufact_infor pmi) and pmi.batch_product_id = ?1 and pmi.is_delete = ?2", nativeQuery = true)
    ProductManufactInfor findMaxProductManufactInforByBatchProductIdAndDelete(String batchProductId, Integer delete);


    @Query(value = "select * from t_om_product_manufact_infor pmi where pmi.batch_product_code = ?1 and pmi.process_status = ?2 and pmi.is_delete = ?3 order by sequence asc", nativeQuery = true)
    List<ProductManufactInfor> findProductManufactInforsByBatchProductCodeAndProcessStatusNotAndDeleteOrderBySequenceAsc(String batchProductCode, Integer processStatus, Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductCodeAndSequenceIsLessThanAndDelete(String batchProductCode,int sequence, Integer delete);


    List<ProductManufactInfor> findProductManufactInforsByBatchProductCodeAndIsProductionProcessAndSequenceIsLessThanAndDelete(String batchProductCode,int isProProcess,int sequence, Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByBatchProductCodeAndDeleteOrderBySequenceAsc(String productCode, int delete);

    int countProductManufactInforsByBatchProductIdAndProcessStatusNotAndDelete(String batchProductId,Integer status, Integer delete);


    int countProductManufactInforsByBatchProductIdAndProcessStatusAndDelete(String batchProductId, Integer status, Integer delete);

    int countProductManufactInforsByBatchProductIdAndAlertStatusAndDelete(String batchProductId, Integer status, Integer delete);


    int countProductManufactInforsByBatchProductIdAndDelete(String batchProductId, Integer delete);

    List<ProductManufactInfor> findProductManufactInforsByWorkOrderInforCodeAndDelete(String wo, Integer delete);
}